<?php
/**
 * 日销量统计报表品类 (FBA库存)
 * (model)
 */
namespace App\Models\SalesReport;

use App\Models\Basics\SysArea;
use App\Models\Basics\SysPlat;
use App\Models\Product\ProductClassify;
use Illuminate\Database\Eloquent\Model;

class DailyCv extends Model
{
    /**
     * 日销量统计报表-品类
     * @var string
     */
    protected $table = 'sr_daily_cv';

    protected $guarded = [];

    public $timestamps = false;

    const V_NUM = 30;

    public function classify(){
        return $this->belongsTo(ProductClassify::class,'classify_id');
    }

    public function cvs(){
        return $this->hasMany(DailyCvs::class,'cv_id','id');
    }

    public function cva(){
        return $this->hasMany(DailyCva::class,'cv_id','id');
    }

    public function plat(){
        return $this->belongsTo(SysPlat::class,'plat_id','plat_id');
    }

    public static function headersField(){
        $fields = [];
        $fields['season'] = ['title' => '季节性','sumField' => false];
        $fields['classify'] = ['title' => '品类','sumField' => false];
        for($i = self::V_NUM; $i >= 1; $i--){
            $fields['day'.$i] = [
                'title' => date("m.d",strtotime("-$i days")),
                'sumField' => true
            ];
        }
        $fields['avg3'] = ['title' => '3天AVG','sumField' => true];
        $fields['avg7'] = ['title' => '7天AVG','sumField' => true];
        $fields['avg15'] = ['title' => '15天AVG','sumField' => true];
        $fields['avg30'] = ['title' => '30天AVG','sumField' => true];
        $fields['avg'] = ['title' => 'AVG','sumField' => false];
        $fields['sales_status'] = ['title' => '销量趋势','sumField' => false];
        $fields['fba_instant_num'] = ['title' => 'FBA在库','sumField' => true];
        $fields['fba_transit_num'] = ['title' => 'FBA在途','sumField' => true];
        $fields['cn_instant_num'] = ['title' => '国内在库','sumField' => true];
        $fields['cn_transit_num'] = ['title' => '国内在途','sumField' => true];
        $fields['os_instant_num'] = ['title' => '海外仓在库','sumField' => true];
        $fields['os_transit_num'] = ['title' => '海外仓在途','sumField' => true];
        $fields['inv_factor'] = ['title' => 'FBA&ALL库存系数','sumField' => false];
        return $fields;
    }

    /**
     * 获取平均销量和库存的汇总
     * @param null $platId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @return \Illuminate\Database\Eloquent\Builder|Model|\Illuminate\Database\Query\Builder|object|null
     */
    public static function getAvgDayAndInventorySum($platId = null, $classifyId = null, $season = null, $reportStatus = null){
        $query = self::query()
            ->selectRaw('sum(avg3) as avg3,
            sum(avg7) as avg7,
            sum(avg15) as avg15,
            sum(avg30) as avg30,
            sum(avg) as avg,
            sum(fba_instant_num) as fba_instant_num,
            sum(fba_transit_num) as fba_transit_num,
            sum(cn_instant_num) as cn_instant_num,
            sum(cn_transit_num) as cn_transit_num,
            sum(os_instant_num) as os_instant_num,
            sum(os_transit_num) as os_transit_num');
        if ($platId) $query->where('plat_id', $platId);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($season) {
            $query->whereHas('classify', function ($query) use($season){
                $query->where('season', $season);
            });
        }
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }
        return $query->first();
    }

    /**
     * 获取ID
     * @param array $platId
     * @param null $classifyId
     * @param null $season
     * @param null $reportStatus
     * @return array
     */
    public static function getDailyCvIds($platId = [], $classifyId = null, $season = null, $reportStatus = null){
        $query = self::query();
        if ($platId) $query->whereIn('plat_id', $platId);
        if ($classifyId) $query->where('classify_id', $classifyId);
        if ($season) {
            $query->whereHas('classify', function ($query) use($season){
                $query->where('season', $season);
            });
        }
        if ($reportStatus) {
            $query->whereHas('classify', function ($query) use($reportStatus){
                $query->where('report_status', $reportStatus);
            });
        }
        return $query->pluck('id')->toArray();
    }
}
